{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Vertex AI SDK - AutoML Forecasting Model Training Example",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.6"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "ur8xi4C7S06n"
      },
      "source": [
        "# Copyright 2021 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eHLV0D7Y5jtU"
      },
      "source": [
        "# Vertex AI Model Builder SDK: AutoML Forecasting Model Training Example\n",
        "\n",
        "To use this Colaboratory notebook, you copy the notebook to your own Google Drive and open it with Colaboratory (or Colab). You can run each step, or cell, and see its results. To run a cell, use Shift+Enter. Colab automatically displays the return value of the last line in each cell. For more information about running notebooks in Colab, see the [Colab welcome page](https://colab.research.google.com/notebooks/welcome.ipynb).\n",
        "\n",
        "This notebook demonstrates how to create an AutoML model based on a time series dataset. It will require you provide a bucket where the dataset will be stored.\n",
        "\n",
        "Note: you may incur charges for training, prediction, storage or usage of other GCP products in connection with testing this SDK."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lld3eeJUs5yM"
      },
      "source": [
        "# Install Vertex AI SDK, Authenticate, and upload of a Dataset to your GCS bucket\n",
        "\n",
        "After the SDK installation the kernel will be automatically restarted. You may see this error message `Your session crashed for an unknown reason` which is normal."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cMZLb8Arr2AG"
      },
      "source": [
        "%%capture\n",
        "!pip3 uninstall -y google-cloud-aiplatform\n",
        "!pip3 install google-cloud-aiplatform\n",
        " \n",
        "import IPython\n",
        " \n",
        "app = IPython.Application.instance()\n",
        "app.kernel.do_shutdown(True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ApsLDJjdsGPN"
      },
      "source": [
        "import sys\n",
        "\n",
        "if \"google.colab\" in sys.modules:\n",
        "    from google.colab import auth\n",
        "\n",
        "    auth.authenticate_user()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c0SNmTBeD2nV"
      },
      "source": [
        "### Enter your project and GCS bucket\n",
        "\n",
        "Enter your Project ID in the cell below. Then run the cell to make sure the Cloud SDK uses the right project for all the commands in this notebook."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s19AzYSGLIb9"
      },
      "source": [
        "**If you don't know your project ID**, you may be able to get your project ID using gcloud."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nwlVqT6RKxG7"
      },
      "source": [
        "import os\n",
        "\n",
        "PROJECT_ID = \"\"\n",
        "\n",
        "# Get your Google Cloud project ID from gcloud\n",
        "if not os.getenv(\"IS_TESTING\"):\n",
        "    shell_output=!gcloud config list --format 'value(core.project)' 2>/dev/null\n",
        "    PROJECT_ID = shell_output[0]\n",
        "    print(\"Project ID: \", PROJECT_ID)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "H5E8VB3jLOFC"
      },
      "source": [
        "Otherwise, set your project ID here."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DrED76XTK9OB"
      },
      "source": [
        "if PROJECT_ID == \"\" or PROJECT_ID is None:\n",
        "    PROJECT_ID = \"[your-project-id]\"  # @param {type:\"string\"}"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zkJk7agzT6F9"
      },
      "source": [
        "If you are in a live tutorial session, you might be using a shared test account or project. To avoid name collisions between users on resources created, you create a timestamp for each instance session, and append it onto the name of resources you create in this tutorial."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qcRkdZBaUAz4"
      },
      "source": [
        "from datetime import datetime\n",
        "\n",
        "TIMESTAMP = datetime.now().strftime(\"%Y%m%d%H%M%S\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TFfpJs3DQsfo"
      },
      "source": [
        "Set the name of your Cloud Storage bucket below. It must be unique across all Cloud Storage buckets.\n",
        "\n",
        "You may also change the REGION variable, which is used for operations throughout the rest of this notebook. Make sure to [choose a region where Vertex AI services are available](https://cloud.google.com/vertex-ai/docs/general/locations#available_regions). You may not use a Multi-Regional Storage bucket for training with Vertex AI."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iqSQT6Z6bekX"
      },
      "source": [
        "BUCKET_NAME = \"gs://[your-bucket-name]\"  # @param {type:\"string\"}\n",
        "REGION = \"[your-region]\"  # @param {type:\"string\"}"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ukGsLjm-Ki14"
      },
      "source": [
        "if BUCKET_NAME == \"\" or BUCKET_NAME is None or BUCKET_NAME == \"gs://[your-bucket-name]\":\n",
        "    BUCKET_NAME = \"gs://\" + PROJECT_ID + \"aip-\" + TIMESTAMP"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-6AQjKlnx0mf"
      },
      "source": [
        "The datasets we are using are samples from the [Iowa Liquor Retail Sales](https://pantheon.corp.google.com/marketplace/product/iowa-department-of-commerce/iowa-liquor-sales) dataset. The training sample contains the sales from 2020 and the prediction sample (used in the batch prediction step) contains the January - April sales from 2021."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "V_T10yTTqcS_"
      },
      "source": [
        "TRAINING_DATASET_BQ_PATH = 'bq://bigquery-public-data:iowa_liquor_sales_forecasting.2020_sales_train'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rk43VP_IqcTE"
      },
      "source": [
        "# Initialize Vertex AI SDK\n",
        "\n",
        "Initialize the *client* for Vertex AI."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VCiC9gBWqcTF"
      },
      "source": [
        "from google.cloud import aiplatform\n",
        "\n",
        "aiplatform.init(project=PROJECT_ID, staging_bucket=BUCKET_NAME)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "35QVNhACqcTJ"
      },
      "source": [
        "# Create a Managed Time Series Dataset from BigQuery\n",
        "\n",
        "This section will create a dataset from a BigQuery table."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4OfCqaYRqcTJ"
      },
      "source": [
        "ds = aiplatform.datasets.TimeSeriesDataset.create(\n",
        "    display_name='iowa_liquor_sales_train',\n",
        "    bq_source=[TRAINING_DATASET_BQ_PATH])\n",
        "\n",
        "ds.resource_name"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6-bBqipfqcTS"
      },
      "source": [
        "# Launch a Training Job to Create a Model\n",
        "\n",
        "Once we have defined your training script, we will create a model."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aA41rT_mb-rV"
      },
      "source": [
        "time_column = \"date\"\n",
        "time_series_identifier_column=\"store_name\"\n",
        "target_column=\"sale_dollars\"\n",
        "\n",
        "job = aiplatform.AutoMLForecastingTrainingJob(\n",
        "    display_name='train-iowa-liquor-sales-automl_1',\n",
        "    optimization_objective='minimize-rmse',    \n",
        "    column_transformations=[\n",
        "        {\"timestamp\": {\"column_name\": time_column}},\n",
        "        {\"numeric\": {\"column_name\": target_column}},\n",
        "        {\"categorical\": {\"column_name\": \"city\"}},\n",
        "        {\"categorical\": {\"column_name\": \"zip_code\"}},\n",
        "        {\"categorical\": {\"column_name\": \"county\"}},\n",
        "    ]\n",
        ")\n",
        "\n",
        "# This will take around an hour to run\n",
        "model = job.run(\n",
        "    dataset=ds,\n",
        "    target_column=target_column,\n",
        "    time_column=time_column,\n",
        "    time_series_identifier_column=time_series_identifier_column,\n",
        "    available_at_forecast_columns=[time_column],\n",
        "    unavailable_at_forecast_columns=[target_column],\n",
        "    time_series_attribute_columns=[\"city\", \"zip_code\", \"county\"],\n",
        "    forecast_horizon=30,\n",
        "    context_window=30,\n",
        "    data_granularity_unit=\"day\",\n",
        "    data_granularity_count=1,\n",
        "    weight_column=None,\n",
        "    budget_milli_node_hours=1000,\n",
        "    model_display_name=\"iowa-liquor-sales-forecast-model\", \n",
        "    predefined_split_column_name=None,\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "muSC-mvgHno7",
        "cellView": "form"
      },
      "source": [
        "#@title # Fetch Model Evaluation Metrics\n",
        "#@markdown Fetch the model evaluation metrics calculated during training on the test set.\n",
        "\n",
        "import pandas as pd\n",
        "\n",
        "list_evaluation_pager = model.api_client.list_model_evaluations(parent=model.resource_name)\n",
        "for model_evaluation in list_evaluation_pager:\n",
        "  metrics_dict = {m[0]: m[1] for m in model_evaluation.metrics.items()}\n",
        "  df = pd.DataFrame(metrics_dict.items(), columns=[\"Metric\", \"Value\"])\n",
        "  print(df.to_string(index=False))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nIw1ifPuqcTb"
      },
      "source": [
        "# Run Batch Prediction"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nT-bZ1autijD"
      },
      "source": [
        "#@markdown ## Create Output BigQuery Dataset\n",
        "#@markdown First, create a new BigQuery dataset for the batch prediction output in the same region as the batch prediction input dataset. \n",
        "\n",
        "import os\n",
        "from google.cloud import bigquery\n",
        "\n",
        "os.environ[\"GOOGLE_CLOUD_PROJECT\"] = PROJECT_ID\n",
        "\n",
        "batch_predict_bq_input_uri = \"bq://bigquery-public-data.iowa_liquor_sales_forecasting.2021_sales_predict\"\n",
        "batch_predict_bq_output_dataset_name = \"iowa_liquor_sales_predictions\"\n",
        "batch_predict_bq_output_dataset_path = \"{}.{}\".format(PROJECT_ID, batch_predict_bq_output_dataset_name)\n",
        "batch_predict_bq_output_uri_prefix = \"bq://{}.{}\".format(PROJECT_ID, batch_predict_bq_output_dataset_name)\n",
        "# Must be the same region as batch_predict_bq_input_uri\n",
        "client = bigquery.Client()\n",
        "dataset = bigquery.Dataset(batch_predict_bq_output_dataset_path)\n",
        "dataset_region = \"US\" # @param {type : \"string\"}\n",
        "dataset.location = dataset_region\n",
        "dataset = client.create_dataset(dataset)\n",
        "print(\"Created bigquery dataset {} in {}\".format(batch_predict_bq_output_dataset_path, dataset_region))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "krKRn9W0xxI2"
      },
      "source": [
        "Run a batch prediction job to generate liquor sales forecasts for stores in Iowa from an input dataset containing historical sales."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8I8aRjRh6GGG"
      },
      "source": [
        "model.batch_predict(\n",
        "   bigquery_source=batch_predict_bq_input_uri,\n",
        "   instances_format=\"bigquery\",\n",
        "   bigquery_destination_prefix=batch_predict_bq_output_uri_prefix,\n",
        "   predictions_format=\"bigquery\",\n",
        "   job_display_name=\"predict-iowa-liquor-sales-automl_1\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CTQl3fH6Ur2Z",
        "cellView": "form"
      },
      "source": [
        "#@title # Visualize the Forecasts\n",
        "#@markdown Follow the given link to visualize the generated forecasts in [Data Studio](https://support.google.com/datastudio/answer/6283323?hl=en).\n",
        "\n",
        "import urllib\n",
        "\n",
        "tables = client.list_tables(batch_predict_bq_output_dataset_path)\n",
        "\n",
        "prediction_table_id = \"\"\n",
        "for table in tables:\n",
        "  if table.table_id.startswith(\n",
        "      \"predictions_\") and table.table_id > prediction_table_id:\n",
        "    prediction_table_id = table.table_id\n",
        "batch_predict_bq_output_uri = \"{}.{}\".format(\n",
        "    batch_predict_bq_output_dataset_path, prediction_table_id)\n",
        "\n",
        "\n",
        "def _sanitize_bq_uri(bq_uri):\n",
        "  if bq_uri.startswith(\"bq://\"):\n",
        "    bq_uri = bq_uri[5:]\n",
        "  return bq_uri.replace(\":\", \".\")\n",
        "\n",
        "\n",
        "def get_data_studio_link(batch_prediction_bq_input_uri,\n",
        "                         batch_prediction_bq_output_uri, time_column,\n",
        "                         time_series_identifier_column, target_column):\n",
        "  batch_prediction_bq_input_uri = _sanitize_bq_uri(\n",
        "      batch_prediction_bq_input_uri)\n",
        "  batch_prediction_bq_output_uri = _sanitize_bq_uri(\n",
        "      batch_prediction_bq_output_uri)\n",
        "  base_url = \"https://datastudio.google.com/c/u/0/reporting\"\n",
        "  query = \"SELECT \\\\n\" \\\n",
        "  \" CAST(input.{} as DATETIME) timestamp_col,\\\\n\" \\\n",
        "  \" CAST(input.{} as STRING) time_series_identifier_col,\\\\n\" \\\n",
        "  \" CAST(input.{} as NUMERIC) historical_values,\\\\n\" \\\n",
        "  \" CAST(predicted_{}.value as NUMERIC) predicted_values,\\\\n\" \\\n",
        "  \" * \\\\n\" \\\n",
        "  \"FROM `{}` input\\\\n\" \\\n",
        "  \"LEFT JOIN `{}` output\\\\n\" \\\n",
        "  \"ON\\\\n\" \\\n",
        "  \"CAST(input.{} as DATETIME) = CAST(output.{} as DATETIME)\\\\n\" \\\n",
        "  \"AND CAST(input.{} as STRING) = CAST(output.{} as STRING)\"\n",
        "  query = query.format(time_column, time_series_identifier_column,\n",
        "                       target_column, target_column,\n",
        "                       batch_prediction_bq_input_uri,\n",
        "                       batch_prediction_bq_output_uri, time_column, time_column,\n",
        "                       time_series_identifier_column,\n",
        "                       time_series_identifier_column)\n",
        "  params = {\n",
        "      \"templateId\": \"067f70d2-8cd6-4a4c-a099-292acd1053e8\",\n",
        "      \"ds0.connector\": \"BIG_QUERY\",\n",
        "      \"ds0.projectId\": PROJECT_ID,\n",
        "      \"ds0.billingProjectId\": PROJECT_ID,\n",
        "      \"ds0.type\": \"CUSTOM_QUERY\",\n",
        "      \"ds0.sql\": query\n",
        "  }\n",
        "  params_str_parts = []\n",
        "  for k, v in params.items():\n",
        "    params_str_parts.append(\"\\\"{}\\\":\\\"{}\\\"\".format(k, v))\n",
        "  params_str = \"\".join([\"{\", \",\".join(params_str_parts), \"}\"])\n",
        "  return \"{}?{}\".format(base_url,\n",
        "                        urllib.parse.urlencode({\"params\": params_str}))\n",
        "\n",
        "\n",
        "print(\n",
        "    get_data_studio_link(batch_predict_bq_input_uri,\n",
        "                         batch_predict_bq_output_uri, time_column,\n",
        "                         time_series_identifier_column, target_column))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "24NPJ7nCRchZ"
      },
      "source": [
        "\n",
        "# Cleaning up\n",
        "\n",
        "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n",
        "\n",
        "Otherwise, you can delete the individual resources you created in this tutorial:\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gq3ZSsAkRnXh"
      },
      "source": [
        "# Delete model resource\n",
        "model.delete(sync=True)\n",
        "\n",
        "# Delete Cloud Storage objects that were created\n",
        "! gsutil -m rm -r $BUCKET_NAME"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}